package sqlparser;

import exceptions.NoIndexOnAttributesException;
import modeller.databasedesignmodel.Attribute;
import modeller.databasedesignmodel.query.EqualitySearchQuery;
import modeller.databasedesignmodel.query.EqualityWithRangeSearchQuery;
import modeller.databasedesignmodel.query.InsertQuery;
import modeller.databasedesignmodel.query.UpdateDeleteQuery;
import modeller.databasedesignmodel.relation.Relation;

import modeller.databasedesignmodel.relation.index.ITableIndex;
import modeller.databasedesignmodel.relation.index.UniqueClusteredTreeIndex;
import modeller.databasedesignmodel.relation.index.UniqueUnclusteredTreeIndex;
import modeller.databasedesignmodel.transaction.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.StatementVisitor;
import net.sf.jsqlparser.statement.create.table.ColDataType;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.replace.Replace;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import queped.SerializablePair;

import java.util.*;

import static queped.NeptuneHelperFunctions.isInDebugMode;

public class QuePEDSQLStatementVisitor implements StatementVisitor, SelectVisitor, FromItemVisitor {


    // member variables to hold the number of rows per relation
    HashMap<String, Integer> relationNumRows;

    // member variables to hold DB design figures
    double DIOTime;
    double DBPageSize;
    double DBFillFactor;

    // relation being built
    Relation relation;

    // member variables denoting the current queries being built
    HashSet<Attribute> attributesForCurrentQuery;
    Relation currentRelationForQuery;

    int currentBranchNumberOfTransaction = 0;

    // member variables to build up the transaction
    Transaction transaction;
    SQLQuerySequence currentSQLQuerySequence;
    Branch branch;
    Loop loop;
    SQLQuery currentSQLQuery;


    // member variables to hold list of already built transactions and relations
    HashSet<Transaction> transactions = new HashSet<Transaction>();
    HashSet<Relation> relations = new HashSet<Relation>();

    // SQL currentSQLQuery expression visitor
    QuePEDExpressionVisitor expressionVisitor;


    HashMap<String, Relation> mapNameToRelation = new HashMap<String, Relation>();
    private ArrayList<Double> branchProbs = new ArrayList<Double>();
    private ArrayList<Integer> averageNumRows = new ArrayList<Integer>();
    private int currentQueryOfTransaction = 0;

    // number of rows per query
    private String[] numRows;
    private int currentRelationNumRowIndex = 0;
    private int scalingFactor = 1;


    public QuePEDSQLStatementVisitor(int scalingFactor, double DIOTime, double DBPageSize, double DBFillFactor) {
        this.scalingFactor = scalingFactor;
        this.DIOTime = DIOTime;
        this.DBPageSize = DBPageSize;
        this.DBFillFactor = DBFillFactor;
    }

    /* statement visitor */
    @Override
    public void visit(Select select) {

        // start new SQLquery
        startSQLQuery();
        // deal with select statement body
        select.getSelectBody().accept(this);

        finishSQLQuery();
    }

    private void startSQLQuery() {
        resetAttributesForCurrentQuery();
    }

    /* select */
    @Override
    public void visit(PlainSelect plainSelect) {


        // get relation accessed
        FromItem item = plainSelect.getFromItem();
        item.accept(this);

        // initialise currentRelation
        plainSelect.getFromItem().accept(this);

        // visit WHERE clause
        Expression whereClause = plainSelect.getWhere();
        expressionVisitor = new QuePEDExpressionVisitor(currentRelationForQuery);
        whereClause.accept(expressionVisitor);

        // get attributes referenced in WHERE clause
        attributesForCurrentQuery = expressionVisitor.getAttributesForQuery();

        SerializablePair<Relation, HashSet<Attribute>> relationHashSetSerializablePair = new SerializablePair<Relation, HashSet<Attribute>>(currentRelationForQuery, attributesForCurrentQuery);

        // init appropriate SELECT SQL currentSQLQuery
        try {

            // find out whether it is a equality or range scan
            boolean isRange = expressionVisitor.isRange();

            if (isRange) {
                // equality with range search query
                currentSQLQuery = new EqualityWithRangeSearchQuery(relationHashSetSerializablePair);
            } else {
                // find out whether index is defined on the attributes in currentSQLQuery
                ITableIndex index = currentRelationForQuery.getIndexWithAttributes(attributesForCurrentQuery);

                // check whether if the 'equality scan' needs to be changed to become range scan due to their not being only a partial match
                if (!attributesForCurrentQuery.containsAll(index.getAttributeList())) {
                    // this is a range query too because the index doesn't match
                    currentSQLQuery = new EqualityWithRangeSearchQuery(relationHashSetSerializablePair);
                } else {
                    // index defined is exactly on the attributes required = equality search
                    currentSQLQuery = new EqualitySearchQuery(relationHashSetSerializablePair);
                }
            }

        } catch (NoIndexOnAttributesException e) {
            currentSQLQuery = new EqualitySearchQuery(relationHashSetSerializablePair);
        }
    }

    private void finishSQLQuery() {

        // init currentSQLQuerySequence if null
        startCurrentSQLSequenceIfNecessary();
        // add currentSQLQuery to currentSQLQuerySequence
        currentSQLQuerySequence.addSQLStatement(currentSQLQuery);
        currentSQLQuery = null;
        currentQueryOfTransaction++;
        resetAttributesForCurrentQuery();

        if (isInDebugMode()) System.out.println();

    }

    private void resetAttributesForCurrentQuery() {

        this.attributesForCurrentQuery = new HashSet<Attribute>();

    }

    @Override
    public void visit(Delete delete) {

        Expression whereClause = delete.getWhere();
        Table table = delete.getTable();

        processUpdateDeleteQuery(table, whereClause);

    }

    @Override
    public void visit(Update update) {

        Expression whereClause = update.getWhere();
        Table table = update.getTable();

        processUpdateDeleteQuery(table, whereClause);

    }

    private void processUpdateDeleteQuery(Table table, Expression whereClause) {

        // start new Update/Delete SQLquery
        startSQLQuery();

        // populate currentRelationForQuery
        table.accept(this);

        // populate attributesForCurrentQuery by visiting WHERE clause
        expressionVisitor = new QuePEDExpressionVisitor(currentRelationForQuery);
        whereClause.accept(expressionVisitor);
        attributesForCurrentQuery = expressionVisitor.getAttributesForQuery();

        // populate relation pair
        SerializablePair<Relation, HashSet<Attribute>> relationHashSetSerializablePair = new SerializablePair<Relation, HashSet<Attribute>>(currentRelationForQuery, attributesForCurrentQuery);

        // get number of matching rows
        int numMatchingRows = averageNumRows.get(currentQueryOfTransaction);

        // initialise
        currentSQLQuery = new UpdateDeleteQuery(numMatchingRows, relationHashSetSerializablePair);

        // finish
        finishSQLQuery();
    }

    @Override
    public void visit(Insert insert) {

        // start new SQLquery
        startSQLQuery();

        // find out which relation is accessed
        FromItem item = insert.getTable();
        item.accept(this);

        SerializablePair<Relation, HashSet<Attribute>> relationHashSetSerializablePair = new SerializablePair<Relation, HashSet<Attribute>>(currentRelationForQuery, attributesForCurrentQuery);

        currentSQLQuery = new InsertQuery(relationHashSetSerializablePair);

        finishSQLQuery();

    }

    @Override
    public void visit(Replace replace) {
        // this is an aggregate DML function
        // we assume that it isn't used to manipulate any attributes other than that in the LHS of the WHERE condition

    }

    @Override
    public void visit(Drop drop) {
        // not supported - as emptying and then deleting the table mid-transaction will require two separate transaction classes due to it vastly changing the service demands and invalidating some transactions
    }

    @Override
    public void visit(Truncate truncate) {

        // similar to drop, except the table itself is not deleted
    }

    @Override
    public void visit(CreateTable createTable) {

        // retrieve relevant attributes in CreateTable object
        Table table = createTable.getTable();
        List<ColumnDefinition> cols = createTable.getColumnDefinitions();
        List<Index> indexes = createTable.getIndexes();

        //set num rows for relation
        int numRowsForRelation = Integer.parseInt(numRows[currentRelationNumRowIndex]) * scalingFactor;
        currentRelationNumRowIndex++;

        // get relation name from table
        String relationName = table.getName();

        // initialise new relation
        //relation = new Relation(relationNumRows.get(relationName), DIOTime, DBPageSize, DBFillFactor);
        relation = new Relation(numRowsForRelation, DIOTime, DBPageSize, DBFillFactor);
        relation.setRelationName(relationName);

        // for each column definition, add attribute to relation
        Iterator<ColumnDefinition> colsIterator = cols.listIterator();
        HashMap<String, Attribute> attsHash = new HashMap<String, Attribute>();
        while (colsIterator.hasNext()) {

            // obtain necessary objects
            ColumnDefinition col = colsIterator.next();
            ColDataType colDataType = col.getColDataType();

            // get column name
            String name = col.getColumnName();

            // int get column size
            int size = getColSize(colDataType);
            Attribute attribute = new Attribute(name, size);

            relation.addAttribute(attribute);
            // map name to corresponding attribute
            attsHash.put(name, attribute);

        }

        // deal with indexes defined
        if (indexes != null) {
            Iterator<Index> indexIterator = indexes.listIterator();
            while (indexIterator.hasNext()) {

                Index index = indexIterator.next();

                ArrayList<String> colNamesForIndex = (ArrayList<String>) index.getColumnsNames();
                String indexType = index.getType();

                if (indexType.equals("PRIMARY KEY")) {

                    //generate list of attributes in index
                    LinkedList<Attribute> attsForIndex = new LinkedList<Attribute>();
                    for (String colName : colNamesForIndex) {
                        attsForIndex.add(attsHash.get(colName));
                    }

                    relation.addTableIndex(new UniqueUnclusteredTreeIndex(attsForIndex));
                }
            }
        }
        // map name to corresponding relation
        mapNameToRelation.put(relation.getRelationName(), relation);
        //add to set of available relations
        relations.add(relation);
    }

    public void resetCurrentQuerySequence() {
        currentSQLQuerySequence = null;
    }

    public void startBranch() {

        double trueBranchProb = branchProbs.get(currentBranchNumberOfTransaction);
        // reset branch to new one
        branch = new Branch(trueBranchProb);

    }

    public void startTrueBranch() {
        resetCurrentQuerySequence();
    }

    public void finishTrueBranch() {
        branch.setTrueStatement(currentSQLQuerySequence);
    }

    public void startFalseBranch() {
        resetCurrentQuerySequence();
    }

    public void finishFalseBranch() {
        branch.setFalseStatement(currentSQLQuerySequence);
    }

    public void finishBranch() {

        // init new one
        resetCurrentQuerySequence();
        //add procstmt to current transaction
        transaction.addProcStmt(branch);
        // increment branch number
        currentBranchNumberOfTransaction++;
        // dereference branch
        branch = null;
    }

    public void startLoop(int numIterations) {

        // reset to new loop
        loop = new Loop(numIterations);

    }

    public void startLoopContents() {
        finishCurrentSQLSequenceIfNecessary();
        startCurrentSQLSequenceIfNecessary();
    }

    public void finishLoopContents() {
        loop.setLoopContents(currentSQLQuerySequence);
        resetCurrentQuerySequence();
    }

    public void finishLoop() {
        transaction.addProcStmt(loop);
        loop = null;
    }

    public void startSQLSequence() {
        currentSQLQuerySequence = new SQLQuerySequence();
    }

    public void finishSQLSequence() {

        transaction.addProcStmt(currentSQLQuerySequence);
        resetCurrentQuerySequence();
    }

    public void startTransaction(boolean isOpen, String name, double prevalenceOrRate) {

        transaction = new Transaction(isOpen,name, prevalenceOrRate);
        currentBranchNumberOfTransaction = 0;
        currentQueryOfTransaction = 0;
    }

    public void finishTransaction() {

        transactions.add(transaction);
        finishCurrentSQLSequenceIfNecessary();
        transaction = null;

        branchProbs = new ArrayList<Double>();
        averageNumRows = new ArrayList<Integer>();
    }

    private int getColSize(ColDataType colDataType) {

        String type = colDataType.getDataType();

        if (type.equals("int")
                || type.equals("integer")
                || type.equals("real")
                || type.equals("serial")
                ) {

            return 4;

        } else if (type.equals("smallint")) {
            return 2;
        } else if (type.equals("varchar")
                || type.startsWith("char")
                ) {

            List<String> args = colDataType.getArgumentsStringList();

            if (args != null) {
                // if N <=126 then overhead = 1 byte, else = 4; size of "N" which is specified in the data type,
                // chars are 1 byte each
                int N = Integer.valueOf(args.get(0));
                if (N <= 126) {
                    return 1 + N;
                } else {
                    return 4 + N;
                }

            } else {

                // arbitrary limit to 3996 chars
                return Integer.valueOf(4000);
            }

        } else if (type.equals("date")) {
            return 4;
        } else if (type.equals("timestamp")
                || type.equals("time")
                || type.equals("bigint")
                || type.equals("bigserial")
                || type.equals("double")) {
            return 8;
        }

        return 0;
    }


    public Relation getRelation() {
        return relation;
    }

    public ISQLQuery getCurrentSQLQuery() {
        return currentSQLQuery;
    }

    @Override
    public void visit(Union union) {

    }

    /* fromItem */

    @Override
    public void visit(Table table) {

        currentRelationForQuery = mapNameToRelation.get(table.getName());

    }

    @Override
    public void visit(SubSelect subSelect) {                                         // unnecessary - nested selects not supported
    }

    @Override
    public void visit(SubJoin subJoin) {

        // unnecessary - joins not supported

    }

    public HashSet<Transaction> getTransactions() {
        return transactions;
    }

    /*
    calls finish current proc stmt if necessary
     */
    public void finishCurrentSQLSequenceIfNecessary() {

        if (currentSQLQuerySequence != null) {
            // if in the middle of a SQL sequence, close it off
            finishSQLSequence();
        }
    }

    /*
   calls start current proc stmt if necessary
    */
    public void startCurrentSQLSequenceIfNecessary() {

        if (currentSQLQuerySequence == null) {
            // if not initialised to something already, init it
            startSQLSequence();
        }
    }

    public HashSet<Relation> getRelations() {

        return relations;
    }

    public void addBranchProb(Double prob) {
        this.branchProbs.add(prob);
    }

    public void addNumMatchingRows(int num) {
        averageNumRows.add(num);
    }

    public void setNumRows(String[] numRows) {
        this.numRows = numRows;
    }


}
